package com.xy6.jvm.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

/**
 * 批量插入记录到oracle库，测试性能
 * 
 * @author zhang
 * @since 2018-04-05
 */
public class OracleMain {

	public static void main(String[] args) {
		Connection conn = null;
		conn = getConnection();
		if(null == conn){
			return;
		}
		OracleMain client = new OracleMain();
		List<String> strs = client.getStrings(100000);
		
		// 循环批量插入，记录执行时间
		int count = 10;
		long sum = 0;
		for(int i=0; i<count; i++){
			long time1 = System.currentTimeMillis();
			client.batchInsert(conn, strs);
			long time2 = System.currentTimeMillis();
			sum += time2 - time1;
			System.out.println(String.format("cost time: %d ms", time2 - time1));
		}
		System.out.println(String.format("avg time: %d ms", sum / count));
		
		if (null != conn) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 批量插入记录
	 * 
	 * @param conn
	 * @param strs
	 */
	private void batchInsert(Connection conn, List<String> strs) {
		PreparedStatement ps = null;
		try {
			// 批量插入记录
			String sql = "insert into a0k_batch1(uno) values(?)";
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			for(String elem : strs){
				ps.setString(1, elem);
				ps.addBatch();
			}
			ps.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		}
	}
	
	/**
	 * 获取oracle连接
	 * 
	 * @return
	 */
	private static Connection getConnection(){
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@localhost:1521:orcl";
			conn = DriverManager.getConnection(url, "hn_base", "hn_base");
			return conn;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 随机生成指定数量的数字字符串
	 * 
	 * @return
	 */
	private List<String> getStrings(int n){
		List<String> strs = new ArrayList<>(n);
		Random r = new Random();
		for(int i=0; i<n; i++){
			strs.add(String.valueOf(r.nextInt(10000)));
		}
		return strs;
	}
	
}
